/**
 * title: UserDAO.java
 * description: Program that provides abstract interface to the database with the
 *              help of the User model, which encapsulates user related items.
 * date: December 22 2013
 * id: 2540434
 * course: COMP 495 (Project-Server)
 * @author Mohamed B Thaha
 * @version 1.0
 * @copyright 2013 Mohamed B Thaha
 */

/**
 * DOCUMENTATION...
 */

/**                                                                               
 *
 *<H1>V2_User</H1>
 *
 *<H3>Purpose and Description</H3>
 *
 *<P>
 * An application that allows the job of the DAO (data access object) to provide 
 * an abstract interface to the database.
 *</P>
 *<P>
 * The DAOÕs main purpose is to perform CRUD operations on the database, which basically 
 * means create, retrieve, update, and delete data by virtue of a database connection or 
 * JDBC API driver.
 *</P>
 *<P>
 * This program requires the Sun Java SDK version 1.7 or better.
 *</P>
 *                                                                              
 *<DL>
 *<DT> Compiling and running instructions</DT>
 *<DT> Assuming SDK 1.7 (or later) and the CLASSPATH are set up properly.</DT>
 *<DT> Assuming the use of Eclipse IDE (Kepler) 4.3.1.</DT>
 *<DT> Change to the directory containing the source code.</DT>
 *<DD> Compile:    UserDAO.java</DD>
 *<DD> Run:        UserDAO.java</DD>
 *<DD> Document:   Generate Javadoc UserDAO</DD>
 *</DL>
 */

 /**
 *
 * <H3>Classes</H3>
 *
 *<P>
 * public class UserDAO {<BR>
 * This is the main public class for this application. It contains all the
 * methods required to perform CRUD operations.
 *</P>
 *
 * <H3>UserDAO Methods</H3>
 *
 *<P>
   public static int create(User newUser) throws Exception {<BR>
   This method returns the user id if it was successful or a negative value 
   if it was a failure.
 *</P>
 * 
 *<P>
   public static User select( long userId ) throws Exception {<BR>
   This method returns the first element of a User array if such a user exists.
 *</P>
 * 
 *<P>
   public static User[] select(Hashtable<String, Object> user_filters) throws Exception {<BR>
   This method returns an array of Users if such users exist.
 *</P>
 *
 *<P>
   public static Boolean update(long id, Hashtable<String, Object> args) throws Exception {<BR>
   This method returns either a true value indicating that the update was a success 
   or a false value indicating that the update has failed.
 *</P> 
 *
 *<P>
   public static boolean delete(long userID, String password) throws Exception {<BR>
   This method returns either a true value indicating that the user and all his or her information 
   has been deleted or a false value indicating that the deletion has failed.
 *</P> 
 * 
 * 
 * <H3>UserDAO Instance Variables (Tags - Constants)</H3>
 *
 *<P>
 * private static final String USER_ID = "user_id"
 * private static final String FIRST_NAME = "first_name"
 * private static final String LAST_NAME = "last_name"
 * private static final String BIRTHDAY = "birthday"
 * private static final String TELEPHONE = "telephone"
 * private static final String EMAIL = "email"
 * private static final String OTHER_PREF = "other_pref"
 * private static final String PASSWORD = "password"
 *</P>
 */

/**
 * CODE...
 */

package athabasca.ca.dao;
/** Java core packages */
import java.sql.*;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import athabasca.ca.model.User;

import athabasca.ca.util.*;
/** primary (public) class for UserDAO */
public class UserDAO {
	public static final String USER_ID = "user_id";
	public static final String FIRST_NAME = "first_name";
	public static final String LAST_NAME = "last_name";
	public static final String BIRTHDAY = "birthday";
	public static final String TELEPHONE = "telephone";
	public static final String EMAIL = "email";
	public static final String OTHER_PREF = "other_pref";
	public static final String PASSWORD = "password";
	
	/**
	 *  Method's purpose is to insert a new user into the database
	 * @param newUser
	 * holds user information
	 * @return
	 * int - the user id confirming a successful insert or a negative integer
	 * explaining a failed insert
	 * @throws Exception
	 */
	public static int create(User newUser) throws Exception {
        Connection conn = null;
        PreparedStatement query = null;
        PreparedStatement query2 = null;
        boolean isTrue = false;
        int userID = 1;
        
        // A 'safeguard' to make sure that we only allow the server to create the
        // user id and make sure a password is present.
        try {
        	if ((newUser.getUserId() != 0) || (newUser.getPassword() == null) 
        			|| (newUser.getFirstName() == null) || (newUser.getLastName() == null)) {
        		return -1;
        	}
        	else {
        		String sql = "";
        		
        		int returnValueUser;
        		int returnValueContactInfo;
        	
        		conn = postgreSQL.postgreSQLConn();
        	
        		String first_name = newUser.getFirstName();
        		String last_name = newUser.getLastName();
        		String birthday = newUser.getBirthday();
        		String[] telephone = newUser.getTelephones();
        		String[] email = newUser.getEmails();
        		String other_pref = newUser.getOtherPreferences();
        		String password = newUser.getPassword();
                    
        		// This query will only insert data into the user_profiles table.
        		query = conn.prepareStatement("INSERT INTO user_profiles " +
        				"(first_name, last_name, birthday, other_pref, password) " +
        				"VALUES (?, ?, ?, ?, ?)");
        		
        		query.setObject(1, first_name);
        		query.setObject(2, last_name);
        		query.setObject(3, birthday);
        		query.setObject(4, other_pref);
        		query.setObject(5, password);
        		
        		returnValueUser = query.executeUpdate();
                
                // The purpose of this sql statement is to retrieve the newly-created 
                // database-assigned user_id.
                sql = ("SELECT user_id" +
                                " FROM user_profiles" +
                                " WHERE user_id = (SELECT MAX(user_id) FROM user_profiles)");
                // This query will select the user_id based on the important user-related 
                // information.
                query = conn.prepareStatement(sql);
        
                ResultSet results = query.executeQuery();
    
                // Retrieve results while there exists a user id.
                while (results.next()) {
                        userID = results.getInt(USER_ID);
                }
        
                query.close();
              
        		Array telephone_cast = conn.createArrayOf("varchar", telephone);
        		Array email_cast = conn.createArrayOf("varchar", email);
                
        		// This query will only insert data into the contact_info table as
        		// it relates to the user_profiles table.
        		query2 = conn.prepareStatement("INSERT INTO contact_info " + 
                        	"VALUES (?, ?, ?)");
        		
        		query2.setObject(1, telephone_cast);
        		query2.setObject(2, email_cast);
        		query2.setObject(3, userID);
        		returnValueContactInfo = query2.executeUpdate();
        		
        		query2.close();
        	
        		// We must make sure that data was inserted in both the user_profiles
        		// table and the contact_info table.
        		if ((returnValueUser > 0) && (returnValueContactInfo > 0)) {
        			isTrue = true;
        		}
        	}
                
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        } 
        
        // A successful insert returns the new database-assigned user id while
        // a failed insert returns a negative value.
        if (isTrue == true)
        	return userID;
        else
        	return -1;
	}

	/** 
	 * Method's purpose is to retrieve the first selected index of the User array (if it exists)
	 * @param userId
	 * the user's identification number
	 * @return
	 * User
	 * @throws Exception
	 */
	public static User select( long userId ) throws Exception {
		Hashtable<String, Object> filters = new Hashtable<String, Object>( );
		filters.put( USER_ID , userId );
                
		User[] results = select( filters );
		if ( results != null )
			return results [ 0 ];
                
		return null;
	}
      
	/**
	 *  Method's purpose is to retrieve an array of selected Users
	 * @param user_filters
	 * hash table
	 * @return
	 * User[]
	 * @throws Exception
	 */
	public static User[] select(Hashtable<String, Object> user_filters) throws Exception {
		PreparedStatement query = null;
		Connection conn = null;
    
		ArrayList<User> user_list = new ArrayList<User>();

		try {
			// If the filters contained in the hash table are empty, return a null value 
			// and do not proceed to execute the rest of the code.
			if (user_filters.isEmpty())
				return null;
			else {
				User setUser;
    	
				String sql = "";
				String statement = "";
				String statement2 = "";
				String key = "";
                        
				Enumeration<String> keys = user_filters.keys( );
        
				// Build the 'statement' on the fly.
				while( keys.hasMoreElements( ) ) {
					key = keys.nextElement( );
					if (key == EMAIL || key == TELEPHONE)
						statement2 = "contact_info.";
					else
						statement2 = "user_profiles.";
                
					if (key == TELEPHONE) {
						String[] telephones = (String[]) user_filters.get(key);
						String newTele = null;
        		
						int i = 0;
        		
						if (telephones.length > 0) {
							newTele = "{" + telephones[i];
							++i;
							for (; i < telephones.length; i++) {
								newTele = newTele + ", " + telephones[i];
							}
							newTele = newTele + "}";
						}
        		
						statement = statement + statement2 + key + " = '" + newTele + "'";
					}
					else if (key == EMAIL) {
						String[] emails = (String[]) user_filters.get(key);
						String newEmails = null;
        		
						int i = 0;
        		
						if (emails.length > 0) {
							newEmails = "{" + emails[i];
							++i;
							for (; i < emails.length; i++) {
								newEmails = newEmails + ", " + emails[i];
							}
							newEmails = newEmails + "}";
						}
        		
						statement = statement + statement2 + key + " = '" + newEmails + "'";
					}
					else if ((key != USER_ID) || (key != TELEPHONE) || (key != EMAIL))
						statement = statement + statement2 + key + " = '" + user_filters.get(key) + "'";
					else
						statement = statement + statement2 + key + " = " + user_filters.get(key);
                                
					if (keys.hasMoreElements())
						statement = statement + " AND ";
				}
        
				sql = ("SELECT *" +
						" FROM user_profiles" +
						" INNER JOIN contact_info" +
						" ON user_profiles.user_id = contact_info.user_id" +
						" WHERE " + statement);

				conn = postgreSQL.postgreSQLConn();
        
				// This query selects all data from the user_profiles table and the
				// contact_info table.
				query = conn.prepareStatement(sql);
                        
				ResultSet results = query.executeQuery();
        
				Array telephone_cast;
				String[] telephone;
				Array email_cast;
				String[] email;

				// If the results returned from the database was empty, return a null value 
				// and there is not reason to continue to execute the rest of the code.
				if (results.wasNull())
					return null;
				else {
        
					while (results.next()) {
						setUser = new User();
        	
						setUser.setUserId(results.getInt(USER_ID));
						setUser.setFirstName(results.getString(FIRST_NAME));
						setUser.setLastName(results.getString(LAST_NAME));
						setUser.setBirthday(results.getString(BIRTHDAY));
						setUser.setOtherPreferences(results.getString(OTHER_PREF));
						setUser.setPassword(results.getString(PASSWORD));

						telephone_cast = results.getArray(TELEPHONE);
						telephone = (String[]) telephone_cast.getArray();
						email_cast = results.getArray(EMAIL);
						email = (String[]) email_cast.getArray();
        	
						setUser.addTelephones(telephone);
            
						setUser.addEmails(email);
            
						user_list.add(setUser);
					}
				}
                        
				query.close();
				}
        
		} catch (ArrayIndexOutOfBoundsException e) {
			System.err.println ( e.getMessage( ) );
			e.printStackTrace ( );
		} catch (NullPointerException e) {
			System.err.println ( e.getMessage( ) );
			e.printStackTrace ( );
		} catch (SQLException sqlError) {
			System.err.println ( sqlError.getMessage( ) );
			sqlError.printStackTrace ( );
		} catch (Exception e) {
			System.err.println ( e.getMessage( ) );
			e.printStackTrace();
		} finally {
			if (conn != null) conn.close();
		}
    
		User[] userList =  (User[]) user_list.toArray(new User[user_list.size()]);
    
		if (userList.length > 0)
			return userList;
		else
			return null;
    }

/**
 *  Method's purpose is to update user related information based on the provided hash
 *  table filters
 * @param id
 * user's identification number
 * @param args
 * hash table
 * @return
 * boolean
 * @throws Exception
 */
public static boolean update(long id, Hashtable<String, Object> args) throws Exception {
	Connection conn = null;
    PreparedStatement query = null;
    boolean isTrue = false;
      
    try {
    	// If the filters contained in the hash table are empty, return a null value 
    	// and do not proceed to execute the rest of the code.
    	if ((args.isEmpty()) || (id <= 0))
    		return false;
    	else {
        String sql = "";
        String sql2 = null;
        String key;
        String f_name, l_name, b_day, other, pass;
        key = f_name = l_name = b_day = other = pass = null;
        String[] tele;
        String[] email;
                
        conn = postgreSQL.postgreSQLConn();
                
        Enumeration<String> keys = args.keys( );
          
        // Build the 'statement' on the fly.
        while (keys.hasMoreElements()) {
                key = keys.nextElement();
                
                sql = ("UPDATE user_profiles"
                                + " SET " + key + " = ?"
                        + " WHERE user_id = " + id);
                
                // For contact_info table; includes telephones and emails.
                sql2 = ("UPDATE contact_info"
                                + " SET " + key + " = ?"
                        + " WHERE user_id = " + id);

                switch (key) {
                	case FIRST_NAME:
                    {
                    	f_name = (String) args.get(FIRST_NAME);
                        query = conn.prepareStatement(sql);
                        query.setObject(1, f_name);
                        int update = query.executeUpdate();
                        query.close();
                        isTrue = true;
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case LAST_NAME:
                    {
                        l_name = (String) args.get(LAST_NAME);
                        query = conn.prepareStatement(sql);
                        query.setObject(1, l_name);
                        int update = query.executeUpdate();
                        query.close();
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case BIRTHDAY:
                    {
                        b_day = (String) args.get(BIRTHDAY);
                        query = conn.prepareStatement(sql);
                        query.setObject(1, b_day);
                        int update = query.executeUpdate();
                        query.close();
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case OTHER_PREF:
                    {
                        other = (String) args.get(OTHER_PREF);
                        query = conn.prepareStatement(sql);
                        query.setObject(1, other);
                        int update = query.executeUpdate();
                        query.close();
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case PASSWORD:
                    {
                        pass = (String) args.get(PASSWORD);
                        query = conn.prepareStatement(sql);
                        query.setObject(1, pass);
                        int update = query.executeUpdate();
                        query.close();
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case TELEPHONE:
                    {
                    	tele = (String[]) args.get(TELEPHONE);
                        Array telephone_cast = conn.createArrayOf("varchar", tele);
                        query = conn.prepareStatement(sql2);
                        query.setObject(1, telephone_cast);
                        int update = query.executeUpdate();
                        query.close();
                        // We must make sure that data was updated in the contact_info
                		// table.
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    case EMAIL:
                    {
                        email = (String[]) args.get(EMAIL);
                        Array email_cast = conn.createArrayOf("varchar", email);
                        query = conn.prepareStatement(sql2);
                        query.setObject(1, email_cast);
                        int update = query.executeUpdate();
                        query.close();
                        if (update > 0)
                        	isTrue = true;
                        break;
                    }
                    default:
                        isTrue = false;
                        return isTrue;
                }
            }
    	}
                
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( ); 
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        
        return isTrue;
    }

/**
 *  Method's purpose is to delete a single user granted that the user has 
 *  provided his or her user id and password
 * @param userID
 * user's identification number
 * @param password
 * user's password
 * @return
 * boolean
 * @throws Exception
 */
public static boolean delete(long userID, String password) throws Exception {
	Connection conn = null;
    PreparedStatement query = null;
    boolean isTrue = false;
      
    try {
    	// Assumes that both the user id and password are present.
    	if ((password != null) || (userID <= 0)) {
    		conn = postgreSQL.postgreSQLConn();
                
    		query = conn.prepareStatement("DELETE FROM user_profiles"
    				+ " WHERE user_id = ? AND password = ?");
    		query.setObject(1, userID);
    		query.setObject(2, password);
    		int returnValue = query.executeUpdate();
    		query.close();
    		
    		// We must make sure that data was deleted from the user_profiles
    		// table and any related data from the contact_info table.
    		if (returnValue > 0)
    			isTrue = true;
    	} else
    		isTrue = false;
                
    } catch (ArrayIndexOutOfBoundsException e) {
    	System.err.println ( e.getMessage( ) );
        e.printStackTrace ( );  
        isTrue = false;
    } catch (NullPointerException e) {
    	System.err.println ( e.getMessage( ) );
    	e.printStackTrace ( );
    	isTrue = false;
    } catch (SQLException sqlError) {
    	System.err.println ( sqlError.getMessage( ) );
    	sqlError.printStackTrace ( );
    	isTrue = false;
    } catch (Exception e) {
    	System.err.println ( e.getMessage( ) );
    	e.printStackTrace();
    	isTrue = false;
    } finally {
    	if (conn != null) conn.close();
    }
    
    return isTrue;
	}
} // End of public class UserDAO